﻿using DotNetCommon;
using DotNetCommon.Accessors;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using DotNetCommon.Logger;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
using System.IO;

namespace DBUtil.Builders;

public class UpdateBuilder : WhereBuilder
{
    protected static readonly ILogger<UpdateBuilder> logger = LoggerFactory.CreateLogger<UpdateBuilder>();
    public EnumUpdateBuilderType Type { get; internal set; }
    public TableName TableName { get; set; }
    public object Object { get; protected set; }

    internal UpdateBuilder(DBAccess db, object obj, string tableName) : base(db)
    {
        Type = EnumUpdateBuilderType.UpdateByDictionary;
        Object = obj;
        TableName = db.ParseQuotedName(tableName);
    }

    #region SetColumn & IgnoreColumn
    /// <summary>
    /// 外部set: 最终都转换成 columnName, 而不是 PropName
    /// </summary>
    /// <remarks>注意: col 之所以是 object 是因为还可能是数组格式,如: OnlyColumns...</remarks>
    internal List<SetIgnoreItem> setIgnores = [];
    internal UpdateBuilder ReplaceSetIgnores(List<SetIgnoreItem> newSetIgnores)
    {
        setIgnores = newSetIgnores;
        return this;
    }
    private const string NO_EXPRESSION_MESSAGE = $"""不能在 update.SetColumn 中使用表达式插入, 如果想使用表达式请调用 update.SetColumn()!""";
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.SetColumn("name", "jack")</item>
    /// <item>update.SetColumn("name",new RawString(db.Select&lt;Person>().ToSqlCount()))</item>
    /// </list>
    /// 注意: 下面是禁止的
    /// <list type="bullet">
    /// <item>不能使用表达式或委托, 如: update.SetColumn("name",()=>db.Select&lt;Person>().Count())</item>
    /// </list>
    /// </summary>
    public virtual UpdateBuilder SetColumn(string colname, object constantVal)
    {
        if (constantVal is Expression || constantVal is Delegate) throw new Exception(NO_EXPRESSION_MESSAGE);
        SetIgnoreItem.SetColumn(setIgnores, db.RemoveQuote(colname), constantVal);
        return this;
    }
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.SetColumnIf(true, "name", "jack")</item>
    /// <item>update.SetColumnIf(true, "name",new RawString(db.Select&lt;Person>().ToSqlCount()))</item>
    /// </list>
    /// 注意: 下面是禁止的
    /// <list type="bullet">
    /// <item>不能使用表达式或委托, 如: update.SetColumnIf(true, "name",()=>db.Select&lt;Person>().Count())</item>
    /// </list>
    /// </summary>
    public virtual UpdateBuilder SetColumnIf(bool condition, string colname, object constantVal) => condition ? SetColumn(colname, constantVal) : this;
    /// <summary>
    /// 示例: update.IgnoreColumns("name","age")
    /// </summary>  
    public virtual UpdateBuilder IgnoreColumns(params string[] colNames)
    {
        SetIgnoreItem.IgnoreColumns(setIgnores, colNames.Select(db.RemoveQuote).ToArray());
        return this;
    }
    /// <summary>
    /// 示例: update.IgnoreColumnsIf(true,"name","age")
    /// </summary>
    public virtual UpdateBuilder IgnoreColumnsIf(bool condition, params string[] colNames) => condition ? IgnoreColumns(colNames) : this;
    /// <summary>
    /// 示例: update.OnlyColumns("name","age")
    /// </summary>
    public virtual UpdateBuilder OnlyColumns(params string[] colNames)
    {
        SetIgnoreItem.OnlyColumns(setIgnores, colNames.Select(db.RemoveQuote).ToArray());
        return this;
    }
    /// <summary>
    /// 示例: update.OnlyColumnsIf(true,"name","age")
    /// </summary>
    public virtual UpdateBuilder OnlyColumnsIf(bool condition, params string[] colNames) => condition ? OnlyColumns(colNames) : this;
    #endregion
    #region getSetDic
    private Dictionary<string, object> GetSetDic()
    {
        Dictionary<string, object> setDic = [];
        if (Object is Dictionary<string, object> tmp) setDic.Add(tmp);
        for (int i = 0; i < setIgnores.Count; i++)
        {
            var item = setIgnores[i];
            if (item.Type == EnumSetIgnoreType.SetColumn)
            {
                foreach (var col in item.ColumnNamesPure)
                    setDic.SetFluent(col, item.Value);
            }
            else if (item.Type == EnumSetIgnoreType.IgnoreColumn)
            {
                for (var j = 0; j < item.ColumnNamesPure.Length; j++)
                    setDic.RemoveFluent(item.ColumnNamesPure[j]).RemoveFluent(item.ColumnNamesPure[j]);
            }
            else if (item.Type == EnumSetIgnoreType.OnlyColumn)
            {
                var reserveKeys = new List<string>();
                reserveKeys.AddRange(item.ColumnNamesPure);
                var allKeys = setDic.Keys.ToList();
                var removeKeys = allKeys.Except(reserveKeys).ToList();
                foreach (var key in removeKeys) setDic.Remove(key);
            }
        }
        return setDic;
    }
    #endregion
    #region 复写 AsTable
    public virtual UpdateBuilder AsTable(string newTableName)
    {
        TableName = db.ParseQuotedName(newTableName);
        return this;
    }
    public virtual UpdateBuilder AsTableIf(bool condition, string newTableName)
        => condition ? AsTable(newTableName) : this;
    public virtual UpdateBuilder AsTable(Func<string, string> func)
    {
        var newTableName = func?.Invoke(TableName.Name);
        if (newTableName.IsNotNullOrWhiteSpace()) TableName.SetName(newTableName);
        return this;
    }
    public virtual UpdateBuilder AsTableIf(bool condition, Func<string, string> func)
        => condition ? AsTable(func) : this;
    public virtual UpdateBuilder AsTable(Action<TableName> func)
    {
        func?.Invoke(TableName);
        return this;
    }
    public virtual UpdateBuilder AsTableIf(bool condition, Action<TableName> func)
        => condition ? AsTable(func) : this;
    #endregion
    #region 复写 WhereSeg
    public override UpdateBuilder WhereSeg<TAny>(Expression<Func<TAny, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder;
    public override UpdateBuilder WhereSegIf<TAny>(bool condition, Expression<Func<TAny, bool>> filter)
        => base.WhereSegIf(condition, filter) as UpdateBuilder;
    public override UpdateBuilder WhereSeg<TAny, TAny2>(Expression<Func<TAny, TAny2, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder;
    public override UpdateBuilder WhereSegIf<TAny, TAny2>(bool condition, Expression<Func<TAny, TAny2, bool>> filter)
        => base.WhereSegIf(condition, filter) as UpdateBuilder;
    public override UpdateBuilder WhereSeg<TAny, TAny2, TAny3>(Expression<Func<TAny, TAny2, TAny3, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder;
    public override UpdateBuilder WhereSegIf<TAny, TAny2, TAny3>(bool condition, Expression<Func<TAny, TAny2, TAny3, bool>> filter)
        => base.WhereSegIf(condition, filter) as UpdateBuilder;
    public override UpdateBuilder WhereSeg<TAny, TAny2, TAny3, TAny4>(Expression<Func<TAny, TAny2, TAny3, TAny4, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder;
    public override UpdateBuilder WhereSegIf<TAny, TAny2, TAny3, TAny4>(bool condition, Expression<Func<TAny, TAny2, TAny3, TAny4, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder;
    #endregion
    #region  复写 Where
    public override UpdateBuilder Where(string filter)
        => base.Where(filter) as UpdateBuilder;
    public override UpdateBuilder WhereIf(bool condition, string filter)
        => base.WhereIf(condition, filter) as UpdateBuilder;
    #endregion
    #region 复写 CommandTimeout
    public override UpdateBuilder CommandTimeout(int timeoutSeconds)
        => base.CommandTimeout(timeoutSeconds) as UpdateBuilder;
    public override UpdateBuilder CommandTimeoutIf(bool condition, int timeoutSeconds)
        => condition ? CommandTimeout(timeoutSeconds) : this;
    #endregion

    #region ToSql ExecuteAffrows(Async)
    /// <summary>
    /// 返回生成的sql
    /// </summary>
    public virtual string ToSql()
    {
        if (TableName.FullNameQuoted.IsNullOrWhiteSpace()) throw new Exception($"必须先指定 TableName, 参照: db.Update(\"test\") 或 update.AsTable(...)");
        if (Filters.IsNullOrEmpty()) throw new Exception("不允许执行无 where 条件的update语句!");
        var setDic = GetSetDic();

        var dic = setDic;
        var keys = dic.Keys.ToList();
        var sb = new StringBuilder();
        sb.Append($"update ").Append(TableName.FullNameQuoted).Append(" set ");
        for (int i = 0, len = keys.Count; i < len; i++)
        {
            var col = keys[i];
            var val = dic[col];
            if (i > 0) sb.Append(',');
            sb.Append("\r\n    ");

            sb.Append(db.AddQuote(col)).Append(" = ").Append(db.ConvertToSqlSeg(val).UnWrap());
        }

        //处理过滤条件
        var filterSql = this.DealFilter(Filters);
        sb.Append("\r\nwhere ").Append(filterSql).Append(';');
        var sql = sb.ToString();
        sb.Clear();
        return sql;
    }

    /// <summary>
    /// 插入并返回受影响的行数
    /// </summary>
    public virtual int ExecuteAffrows()
    {
        var sql = ToSql();
        return RunWriteMonitor(new AfterWriteArgument
        {
            TableName = TableName.FullNameQuoted,
            WriteType = EnumWriteType.Update
        }, () => db.ExecuteSql(sql, CommandType.Text, TimeoutSeconds));
    }

    /// <summary>
    /// 插入并返回受影响的行数
    /// </summary>
    public virtual async Task<int> ExecuteAffrowsAsync(CancellationToken cancellationToken = default)
    {
        var sql = ToSql();
        return await RunWriteMonitorAsync(new AfterWriteArgument
        {
            TableName = TableName.FullNameQuoted,
            WriteType = EnumWriteType.Update
        }, async () => await db.ExecuteSqlAsync(sql, CommandType.Text, TimeoutSeconds, null, cancellationToken));
    }
    #endregion
}

public class UpdateBuilder<T> : UpdateBuilder where T : class, new()
{
    protected static new readonly ILogger<UpdateBuilder<T>> logger = LoggerFactory.CreateLogger<UpdateBuilder<T>>();
    private EntityInfo EntityInfo { get; set; }
    #region 初始化
    internal UpdateBuilder(DBAccess db) : base(db, null, null)
    {
        Type = EnumUpdateBuilderType.UpdateByDto;
        EntityInfo = db.GetEntityInfoInternal<T>();
        TableName = EntityInfo.TableName.CloneTableName();
    }
    public UpdateBuilder<T> SetEntity(T entity)
    {
        if (entity == null) return this;
        Object = entity;
        Type = EnumUpdateBuilderType.UpdateByEntity;
        return this;
    }
    public UpdateBuilder<T> SetEntity(IEnumerable<T> entities)
    {
        if (entities.IsNullOrEmpty()) return this;
        Object = entities;
        Type = EnumUpdateBuilderType.UpdateByEntity;
        return this;
    }
    public UpdateBuilder<T> SetExpr(Expression<Func<T>> expression)
    {
        if (expression == null) return this;
        Object = expression;
        Type = EnumUpdateBuilderType.UpdateByExpression;
        return this;
    }
    public UpdateBuilder<T> SetExpr(Expression<Func<T, T>> expression)
    {
        if (expression == null) return this;
        Object = expression;
        Type = EnumUpdateBuilderType.UpdateByExpression;
        return this;
    }
    public UpdateBuilder<T> SetDto(object dto)
    {
        if (dto is T) throw new Exception($"为防止误操作,禁止在 SetDto 方法中传入 {typeof(T).GetClassFullName()} 实例!");
        Object = dto;
        Type = EnumUpdateBuilderType.UpdateByDto;
        return this;
    }
    #endregion

    #region 复写 AsTable
    public override UpdateBuilder<T> AsTable(string newTableName)
    {
        base.AsTable(newTableName);
        return this;
    }
    public override UpdateBuilder<T> AsTableIf(bool condition, string newTableName)
        => condition ? AsTable(newTableName) : this;
    public override UpdateBuilder<T> AsTable(Func<string, string> func)
        => base.AsTable(func) as UpdateBuilder<T>;
    public override UpdateBuilder<T> AsTableIf(bool condition, Func<string, string> func)
        => condition ? AsTable(func) : this;
    #endregion
    #region 复写 WhereSeg
    public override UpdateBuilder<T> WhereSeg<TAny>(Expression<Func<TAny, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereSegIf<TAny>(bool condition, Expression<Func<TAny, bool>> filter)
        => base.WhereSegIf(condition, filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereSeg<TAny, TAny2>(Expression<Func<TAny, TAny2, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereSegIf<TAny, TAny2>(bool condition, Expression<Func<TAny, TAny2, bool>> filter)
        => base.WhereSegIf(condition, filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereSeg<TAny, TAny2, TAny3>(Expression<Func<TAny, TAny2, TAny3, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereSegIf<TAny, TAny2, TAny3>(bool condition, Expression<Func<TAny, TAny2, TAny3, bool>> filter)
        => base.WhereSegIf(condition, filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereSeg<TAny, TAny2, TAny3, TAny4>(Expression<Func<TAny, TAny2, TAny3, TAny4, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereSegIf<TAny, TAny2, TAny3, TAny4>(bool condition, Expression<Func<TAny, TAny2, TAny3, TAny4, bool>> filter)
        => base.WhereSeg(filter) as UpdateBuilder<T>;
    #endregion
    #region  复写 Where
    public override UpdateBuilder<T> Where(string filter)
        => base.Where(filter) as UpdateBuilder<T>;
    public override UpdateBuilder<T> WhereIf(bool condition, string filter)
        => base.WhereIf(condition, filter) as UpdateBuilder<T>;
    #endregion
    #region 复写 CommandTimeout
    public override UpdateBuilder<T> CommandTimeout(int timeoutSeconds)
        => base.CommandTimeout(timeoutSeconds) as UpdateBuilder<T>;
    public override UpdateBuilder<T> CommandTimeoutIf(bool condition, int timeoutSeconds)
        => condition ? CommandTimeout(timeoutSeconds) : this;
    #endregion
    #region UpdatePrimary
    private bool _isUpdatePrimary = false;
    /// <summary>
    /// 设置主键也要更新, 如果想关闭主键更新参考 <seealso cref="SwitchUpdatePrimary(bool)"/>
    /// </summary>
    public UpdateBuilder<T> UpdatePrimary() => SwitchUpdatePrimary(true);
    /// <summary>
    /// 设置主键是否也要更新 传入true表示主键要更新, false表示不更新
    /// </summary>
    public UpdateBuilder<T> SwitchUpdatePrimary(bool isEnable)
    {
        _isUpdatePrimary = isEnable;
        return this;
    }
    #endregion
    #region SetColumn & IgnoreColumn
    private string[] ConvertColumnPureNames(params string[] names)
    {
        var arr = names.Select(name => EntityInfo.Props.FirstOrDefault(col => string.Equals(col.ColumnNamePure, name, StringComparison.OrdinalIgnoreCase) || string.Equals(col.ColumnNameQuoted, name))).ToList();
        var err = string.Empty;
        for (var i = 0; i < arr.Count; i++)
        {
            if (arr[i] == null) err += (err.Length > 0 ? "," : "") + $"【{names[i]}】";
        }
        if (err.Length != 0) throw new Exception($"类({typeof(T).GetClassFullName()})上不存在列({err})!");
        return arr.Select(i => i.ColumnNamePure).ToArray();
    }
    internal new UpdateBuilder<T> ReplaceSetIgnores(List<SetIgnoreItem> newSetIgnores) => base.ReplaceSetIgnores(newSetIgnores) as UpdateBuilder<T>;
    public override UpdateBuilder<T> SetColumn(string colname, object constantVal)
    {
        base.SetColumn(ConvertColumnPureNames(colname).FirstOrDefault(), constantVal);
        return this;
    }
    public override UpdateBuilder<T> SetColumnIf(bool condition, string colname, object constantVal) => base.SetColumnIf(condition, colname, constantVal) as UpdateBuilder<T>;
    public override UpdateBuilder<T> IgnoreColumns(params string[] colNames)
    {
        base.IgnoreColumns(ConvertColumnPureNames(colNames));
        return this;
    }
    public override UpdateBuilder<T> IgnoreColumnsIf(bool condition, params string[] colNames) => base.IgnoreColumnsIf(condition, colNames) as UpdateBuilder<T>;
    public override UpdateBuilder<T> OnlyColumns(params string[] colNames)
    {
        base.OnlyColumns(ConvertColumnPureNames(colNames));
        return this;
    }
    public override UpdateBuilder<T> OnlyColumnsIf(bool condition, params string[] colNames) => base.OnlyColumnsIf(condition, colNames) as UpdateBuilder<T>;

    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.SetColumn(i=>i.Name, "jack")</item>
    /// </list>
    /// </summary>
    public UpdateBuilder<T> SetColumn<TValue>(Expression<Func<T, TValue>> propSelector, TValue constantVal)
    {
        var names = SetIgnoreItem.GetDepthPropNames(propSelector);
        if (names.IsNullOrEmpty()) throw new Exception($"格式错误,无法获取指定的列: {propSelector}!");
        SetIgnoreItem.SetColumn(setIgnores, EntityInfo, names, constantVal);
        return this;
    }
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.SetColumn(i=>i.Age, i=>i.Age+1)</item>
    /// </list>
    /// </summary>
    public UpdateBuilder<T> SetColumn<TValue>(Expression<Func<T, TValue>> propSelector, Expression<Func<T, TValue>> setExpr)
    {
        var names = SetIgnoreItem.GetDepthPropNames(propSelector);
        if (names.IsNullOrEmpty()) throw new Exception($"格式错误,无法获取指定的列: {propSelector}!");
        SetIgnoreItem.SetColumn(setIgnores, EntityInfo, names, setExpr);
        return this;
    }
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.SetColumnIf(true, i=>i.Name, "jack")</item>
    /// </list>
    /// </summary>
    public UpdateBuilder<T> SetColumnIf<TValue>(bool condition, Expression<Func<T, TValue>> propSelector, TValue value)
        => condition ? SetColumn(propSelector, value) : this;
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.SetColumnIf(true, i=>i.Age, i=>i.Age+1)</item>
    /// </list>
    /// </summary>
    public UpdateBuilder<T> SetColumnIf<TValue>(bool condition, Expression<Func<T, TValue>> propSelector, Expression<Func<T, TValue>> setExpr)
        => condition ? SetColumn(propSelector, setExpr) : this;
    private List<string> SetRawItems = null;
    /// <summary>
    /// 示例: 
    /// <list type="bullet">
    /// <item>update.SetRaw("name='tom'")</item>
    /// </list>
    /// </summary>
    public virtual UpdateBuilder<T> SetRaw(string str)
    {
        if (str.IsNullOrWhiteSpace()) return this;
        SetRawItems ??= [];
        SetRawItems.Add(str);
        return this;
    }
    /// <summary>
    /// 示例: 
    /// <list type="bullet">
    /// <item>update.SetRawIf(true, "name='tom'")</item>
    /// </list>
    /// </summary>
    public virtual UpdateBuilder<T> SetRawIf(bool condition, string str)
        => condition ? SetRaw(str) : this;
    /// <summary>
    /// 示例: 
    /// <list type="bullet">
    /// <item>update.ClearAllSetRaw()</item>
    /// </list>
    /// </summary>
    public virtual UpdateBuilder<T> ClearAllSetRaw()
    {
        if (SetRawItems.IsNullOrEmpty()) return this;
        SetRawItems.Clear();
        return this;
    }
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.IgnoreColumns(i => i.Age)</item>
    /// <item>update.IgnoreColumns(i => new { i.Name, i.Age })</item>
    /// </list>
    /// </summary>
    public UpdateBuilder<T> IgnoreColumns(Expression<Func<T, object>> propSelector)
    {
        SetIgnoreItem.IgnoreColumns(setIgnores, EntityInfo, propSelector);
        return this;
    }
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.IgnoreColumnsIf(true, i => i.Age)</item>
    /// <item>update.IgnoreColumnsIf(true, i => new { i.Name, i.Age })</item>
    /// </list>
    /// </summary>
    public UpdateBuilder<T> IgnoreColumnsIf(bool condition, Expression<Func<T, object>> propSelector)
        => condition ? IgnoreColumns(propSelector) : this;
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.OnlyColumns(i => i.Name)</item>
    /// <item>update.OnlyColumns(i => new { i.Name, i.Age })</item>
    /// </list>
    /// </summary>
    /// <remarks>
    /// 注意:
    /// 这个不会影响 update.SetRaw(...), 如果想清空 SetRaw 的设置, 请参考: <seealso cref="ClearAllSetRaw"/>
    /// </remarks>
    public UpdateBuilder<T> OnlyColumns(Expression<Func<T, object>> propSelector)
    {
        SetIgnoreItem.OnlyColumns(setIgnores, EntityInfo, propSelector);
        return this;
    }
    /// <summary>
    /// 示例:
    /// <list type="bullet">
    /// <item>update.OnlyColumnsIf(true, i => i.Name)</item>
    /// <item>update.OnlyColumnsIf(true, i => new { i.Name, i.Age })</item>
    /// </list>
    /// </summary>
    /// <remarks>
    /// 注意:
    /// 这个不会影响 update.SetRaw(...), 如果想清空 SetRaw 的设置, 请参考: <seealso cref="ClearAllSetRaw"/>
    /// </remarks>
    public UpdateBuilder<T> OnlyColumnsIf(bool condition, Expression<Func<T, object>> propSelector)
        => condition ? OnlyColumns(propSelector) : this;
    #endregion
    #region Where
    public UpdateBuilder<T> Where(Expression<Func<T, bool>> filter)
    {
        Filters.Add(filter);
        return this;
    }
    public UpdateBuilder<T> WhereIf(bool condition, Expression<Func<T, bool>> filter)
    {
        if (!condition) return this;
        return Where(filter);
    }
    #endregion

    #region ToSql
    /// <summary>
    /// 只返回生成的 set seg, 如: 
    /// <list type="bullet">
    /// <item>正常的: update test set name='jack',age=20 where id=1</item>
    /// <item>set seg: name='jack',age=20</item>
    /// </list>
    /// </summary>
    internal string ToSqlOnlySetSeg()
    {
        if (Type != EnumUpdateBuilderType.UpdateByEntity) throw new Exception($"ToSqlOnlySetSeg() 只允许 UpdateByEntity!");
        return ToSqlByEntity(true);
    }

    private void ApplyFilters(StringBuilder sb, bool hasWhere)
    {
        if (Filters.IsNotNullOrEmpty())
        {
            var filterSql = this.DealFilter(Filters);
            if (filterSql.IsNotNullOrEmpty())
            {
                if (hasWhere) sb.Append(" and ").Append('(').Append(filterSql).Append(')');
                else sb.Append("\r\nwhere ").Append(filterSql);
            }
        }
    }

    /// <summary>
    /// 返回生成的sql 只需处理 UpdateByExpression/UpdateByDto/UpdateByEntity
    /// </summary>
    public override string ToSql()
    {
        if (Type == EnumUpdateBuilderType.UpdateByDictionary) return base.ToSql();
        if (Type == EnumUpdateBuilderType.UpdateByDto) return ToSqlByDto();
        if (Type == EnumUpdateBuilderType.UpdateByExpression) return ToSqlByExpression();
        if (Type == EnumUpdateBuilderType.UpdateByEntity) return ToSqlByEntity();
        throw new Exception($"错误的类型:{Type}!");
    }
    private string ToSqlByDto()
    {
        var setList = new List<SetListItem>();
        var whereList = new List<EntityPropertyInfo>();//where列
        //允许dto为mull
        ObjectAccessor accessor = null;
        var allProps = EntityInfo.Props.Where(i => !i.IsIgnoreUpdate).ToList();
        if (Object != null)
        {
            //根据dto中定义的列决定更新哪些列,如果其中有主键要考虑主键也更新还是作为过滤条件
            accessor = Accessor.Build(Object);
            var cols = accessor.Properties.Select(i => i.Key);
            foreach (var prop in allProps)
            {
                if (cols.Contains(prop.PropNamePure))
                {
                    //主键默认用作where条件,除非声明主键可写入
                    if (prop.IsPrimaryKey && !_isUpdatePrimary) whereList.Add(prop);
                    else setList.Add(new SetListItem(prop, prop.ColumnNamePure, null, false, null));
                }
            }
        }

        //进行 setIgnore 过滤
        BuilderHelper.ApplySetIgnore(setList, setIgnores, allProps);
        //校验
        if (whereList.IsNullOrEmpty() && Filters.IsNullOrEmpty())
            throw new Exception($"UpdateByDto<{EntityInfo.TypeClassFullName}>:必须指定过滤条件!");

        //先把json列提出来
        var jsonSetList = BuilderHelper.SplitJsonStore(setList);

        var sb = new StringBuilder();
        sb.Append("update ").Append(TableName.FullNameQuoted).Append(" set");
        var colIndex = 0;

        //先处理非json列
        for (int i = 0; i < setList.Count; i++)
        {
            var setItem = setList[i];
            var col = setItem.ColumnNamePure;
            var colQuoted = setItem.PropertyInfo?.ColumnNameQuoted ?? db.AddQuote(col);
            string seg = null;
            var enum2String = setItem.PropertyInfo?.IsEnum == true && setItem.PropertyInfo?.IsDbString == true;
            var activeTimeFormat = setItem.PropertyInfo?.ActiveTimeFormat == true;
            if (setItem.FromSet)
            {
                //来自外部set
                seg = BuilderHelper.GetSqlSegFromVal(db, setItem.Value, activeTimeFormat ? setItem.PropertyInfo.TimeFormat : null, enum2String: enum2String);
            }
            else
            {
                //从实体属性读取值
                seg = db.ConvertToSqlSeg(accessor[Object, setItem.PropertyInfo.PropNamePure], enum2String ? true : activeTimeFormat ? setItem.PropertyInfo.TimeFormat : null).UnWrap();
            }

            if (colIndex > 0) sb.Append(',');
            sb.Append("\r\n    ").Append(colQuoted).Append(" = ").Append(seg);
            colIndex++;
        }

        //再处理json列
        if (jsonSetList.IsNotNullOrEmpty())
        {
            var groups = jsonSetList.GroupBy(i => i.PropertyInfo.ColumnNamePure).ToList();
            for (int i = 0; i < groups.Count; i++)
            {
                var g = groups[i];
                var bucket = g.Key;
                var colList = g.ToList();
                var bucketQuoted = colList.FirstOrDefault(i => i.PropertyInfo?.ColumnNameQuoted != null)?.PropertyInfo?.ColumnNameQuoted ?? db.AddQuote(bucket);
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(bucketQuoted).Append($" = ");

                var seg = GetSetJsonSeg(bucketQuoted, colList, col => accessor[Object, col.PropertyInfo.PropNamePure]);
                sb.Append(seg);

                colIndex++;
            }
        }

        //处理 setRaw
        if (SetRawItems.IsNotNullOrEmpty())
        {
            for (int i = 0; i < SetRawItems.Count; i++)
            {
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(SetRawItems[i]);
                colIndex++;
            }
        }

        //过滤条件
        var hasWhere = false;
        if (whereList.Count != 0)
        {
            //只有whereList
            var tmp = whereList.Select(i => $"{i.ColumnNameQuoted} = {db.ConvertToSqlSeg(accessor[Object, i.PropNamePure]).UnWrap()}").ToStringSeparated(" and ");
            sb.Append($"\r\nwhere {tmp}");
            hasWhere = true;
        }
        //应用Filters
        ApplyFilters(sb, hasWhere);
        sb.Append(';');
        var sql = sb.ToString();
        sb.Clear();
        return sql;
    }
    private string ToSqlByExpression()
    {
        AssertUtil.NotNull(Object);
        var setList = new List<SetListItem>();
        var whereList = new List<EntityPropertyInfo>();//where列
        var allProps = EntityInfo.Props.Where(i => !i.IsIgnoreUpdate).ToList();

        ReadOnlyCollection<MemberBinding> bindings = null;

        //表达式简化时保留 new{} new List{} new[]{}
        var exp = Object as LambdaExpression;
        var reduce = ExpressionHelper.ReduceLambda(exp, BuilderHelper.IsKeepCallBackForUpdate);
        exp = reduce.exp as LambdaExpression;
        var midValues = reduce.midValues;
        var cols = ExpressionHelper.GetInitOrReturnPropNames(exp);
        if (cols.IsNullOrEmpty()) throw new Exception($"UpdateByExpresion<{EntityInfo.TypeClassFullName}>:必须指定更新的内容!");
        foreach (var prop in allProps)
        {
            if (cols.Contains(prop.PropNamePure))
            {
                //主键默认用作where条件,除非声明主键可写入
                if (prop.IsPrimaryKey && !_isUpdatePrimary) whereList.Add(prop);
                else setList.Add(new SetListItem(prop, prop.ColumnNamePure, null, false, null));
            }
        }
        //参数
        var parameters = exp.Parameters;
        //json列不能直接使用精简后的结果,会丢掉指定的更新列
        var body = exp.Body;
        var initExp = body as MemberInitExpression;
        bindings = initExp.Bindings;
        //进行 setIgnore 过滤
        BuilderHelper.ApplySetIgnore(setList, setIgnores, allProps);
        //校验
        if (whereList.IsNullOrEmpty() && Filters.IsNullOrEmpty())
            throw new Exception($"UpdateByExpresion<{EntityInfo.TypeClassFullName}>:必须指定过滤条件!");

        //先把json列提出来
        var jsonSetList = BuilderHelper.SplitJsonStore(setList);

        var sb = new StringBuilder();
        sb.Append("update ").Append(TableName.FullNameQuoted).Append(" set");
        var colIndex = 0;
        //先处理非json列
        for (int i = 0; i < setList.Count; i++)
        {
            var setItem = setList[i];
            var col = setItem.ColumnNamePure;
            var colQuoted = setItem.PropertyInfo?.ColumnNameQuoted ?? db.AddQuote(col);
            string seg = null;
            var enum2String = setItem.PropertyInfo?.IsEnum == true && setItem.PropertyInfo?.IsDbString == true;
            var activeTimeFormat = setItem.PropertyInfo?.ActiveTimeFormat == true;
            if (setItem.FromSet)
            {
                //来自外部set
                seg = BuilderHelper.GetSqlSegFromVal(db, setItem.Value, activeTimeFormat ? setItem.PropertyInfo.TimeFormat : null, enum2String: enum2String);
            }
            else
            {
                //从实体属性读取值
                var bind = bindings.FirstOrDefault(i => i.Member.Name == setItem.PropertyInfo.PropNamePure);
                var assignExp = (bind as MemberAssignment).Expression;
                seg = BuilderHelper.GetSqlSegFromVal(db, assignExp, activeTimeFormat ? setItem.PropertyInfo.TimeFormat : null, parameters, midValues, enum2String: enum2String);
            }

            if (colIndex > 0) sb.Append(',');
            sb.Append("\r\n    ").Append(colQuoted).Append(" = ").Append(seg);
            colIndex++;
        }

        //再处理json列
        if (jsonSetList.IsNotNullOrEmpty())
        {
            var groups = jsonSetList.GroupBy(i => i.PropertyInfo.ColumnNamePure).ToList();
            for (int i = 0; i < groups.Count; i++)
            {
                var g = groups[i];
                var bucket = g.Key;
                var colList = g.ToList();
                var bucketQuoted = colList.FirstOrDefault(i => i.PropertyInfo?.ColumnNameQuoted != null)?.PropertyInfo?.ColumnNameQuoted ?? db.AddQuote(bucket);
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(bucketQuoted).Append($" = ");

                var seg = GetSetJsonSeg(bucketQuoted, colList, col =>
                {
                    var bind = bindings.FirstOrDefault(i => i.Member.Name == col.PropertyInfo.PropNamePure);
                    return (bind as MemberAssignment).Expression;
                }, midValues, parameters);
                sb.Append(seg);
                colIndex++;
            }
        }

        //处理 setRaw
        if (SetRawItems.IsNotNullOrEmpty())
        {
            for (int i = 0; i < SetRawItems.Count; i++)
            {
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(SetRawItems[i]);
                colIndex++;
            }
        }

        //过滤条件
        var hasWhere = false;
        if (whereList.Count != 0)
        {
            var whereListTmp = new List<string>(whereList.Count);
            for (int i = 0; i < whereList.Count; i++)
            {
                var prop = whereList[i];
                string val = null;
                object tmpValue = null;
                var binding = bindings.FirstOrDefault(i => i.Member.Name == prop.PropNamePure);
                if (binding == null) continue;

                var assign = binding as MemberAssignment;
                var exp2 = assign.Expression;
                if (exp2.NodeType == ExpressionType.Constant)
                {
                    tmpValue = (exp2 as ConstantExpression).Value;
                }
                else if (exp2 is ParameterExpression parameter && midValues.TryGetValue(parameter, out object _val))
                {
                    tmpValue = _val;
                }
                else
                {
                    val = BuilderHelper.ParseSql(expression: exp2, db: db,
                        aliasesMap: parameters.Select(i => new KeyValuePair<ParameterExpression, string>(i, null)).ToArray(),
                        parameters: parameters,
                        midValues: midValues);
                }
                if (val == null)
                {
                    val = db.ConvertToSqlSeg(tmpValue).UnWrap();
                }
                whereListTmp.Add($"{prop.ColumnNameQuoted} = {val}");
            }
            var tmp = whereListTmp.ToStringSeparated(" and ");
            sb.Append($"\r\nwhere {tmp}");
            hasWhere = true;
        }
        //应用Filters
        ApplyFilters(sb, hasWhere);
        sb.Append(';');
        var sql = sb.ToString();
        sb.Clear();
        return sql;
    }
    private string ToSqlByEntity(bool onlySet = false)
    {
        AssertUtil.NotNull(Object);
        var allProps = EntityInfo.Props.Where(i => !i.IsIgnoreUpdate).ToList();

        var primaryMulti = EntityInfo.Props.Where(i => i.IsPrimaryKey).ToList();
        var primary = EntityInfo.PrimaryKeyColumn;
        var setList = new List<SetListItem>(allProps.Count + 8);
        var whereList = new List<EntityPropertyInfo>();//where列
        var accessor = Accessor.Build<T>();

        //先将声明的列分为两类: set列 和 where列
        foreach (var prop in allProps)
        {
            //主键默认用作where条件,除非声明主键可写入
            if (prop.IsPrimaryKey && !_isUpdatePrimary) whereList.Add(prop);
            else setList.Add(new SetListItem(prop, prop.ColumnNamePure, null, false, null));
        }
        //进行 setIgnore 过滤
        BuilderHelper.ApplySetIgnore(setList, setIgnores, allProps);
        //校验
        if (whereList.IsNullOrEmpty() && Filters.IsNullOrEmpty() && onlySet == false)
            throw new Exception($"UpdateByEntity<{EntityInfo.TypeClassFullName}>:必须指定过滤条件!");

        //先转换成实体数组
        IEnumerable<T> arr = null;
        if (Object is IEnumerable<T> tmp) arr = tmp;
        else if (Object is T tmpT) arr = [tmpT];
        else throw new Exception("UpdateByEntity 参数错误!");

        //先把json列提出来
        var jsonSetList = BuilderHelper.SplitJsonStore(setList);

        //行数
        var len = arr.Count();
        if (len > 1 && _isUpdatePrimary) throw new Exception($"当更新多行数据时,无法更新主键值!");

        var sb = new StringBuilder();
        if (!onlySet) sb.Append("update ").Append(TableName.FullNameQuoted).Append(" set");
        var colIndex = 0;

        //当多行更新时 加快速度(先把主键的值批量求出来)
        List<List<(string propertyName, object value)>> rowInfos = null;
        if (len > 1)
        {
            var keyNames = primaryMulti.Select(i => i.PropNamePure).ToList();
            //最后一个 AddFluent 是为了给每列提前占个位
            rowInfos = accessor.GetValuesMulti(arr, keyNames.ToList().AddFluent(keyNames.First()));
        }

        //先处理非json列
        for (int i = 0; i < setList.Count; i++)
        {
            var setItem = setList[i];
            var col = setItem.ColumnNamePure;
            var colQuoted = setItem.PropertyInfo?.ColumnNameQuoted ?? db.AddQuote(col);
            var enum2String = setItem.PropertyInfo?.IsEnum == true && setItem.PropertyInfo?.IsDbString == true;
            var activeTimeFormat = setItem.PropertyInfo?.ActiveTimeFormat == true;
            if (setItem.FromSet)
            {
                //实体中未定义列信息
                //来自外部set
                var seg = BuilderHelper.GetSqlSegFromVal(db, setItem.Value, activeTimeFormat ? setItem.PropertyInfo.TimeFormat : null, enum2String: enum2String);
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(colQuoted).Append(" = ").Append(seg);
                colIndex++;
                continue;
            }
            if (len > 1)
            {
                //多行
                //多个entity 需要case...when
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(colQuoted).Append(" = ");
                //如果数据量大 分组是很有必要的, 能减少生成的体积 还能提高性能
                //批量此列获取所有行的值
                var setVals = accessor.GetValues(arr, setItem.PropertyInfo.PropNamePure);
                //放到提前准备好的占位里
                for (int r = 0; r < rowInfos.Count; r++)
                {
                    rowInfos[r][primaryMulti.Count] = (setItem.PropertyInfo.PropNamePure, setVals[r]);
                }

                var caseSeg = GetCasePrimarySegV2(primaryMulti, rowInfos, enum2String, activeTimeFormat ? setItem.PropertyInfo.TimeFormat : null);
                sb.Append(caseSeg);
                colIndex++;
            }
            else
            {
                //单行
                var ent = arr.First();
                var seg = db.ConvertToSqlSeg(accessor[ent, setItem.PropertyInfo.PropNamePure], enum2String ? true : activeTimeFormat ? setItem.PropertyInfo.TimeFormat : null).UnWrap();
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(colQuoted).Append(" = ").Append(seg);
                colIndex++;
            }
        }
        //再处理json列
        if (jsonSetList.IsNotNullOrEmpty())
        {
            //按json列分组 因为支持 .SetColumn(i=>i.Ext.Name,...)
            var groups = jsonSetList.GroupBy(i => i.PropertyInfo.ColumnNamePure).ToList();
            for (int i = 0; i < groups.Count; i++)
            {
                var g = groups[i];
                var bucket = g.Key;
                var colList = g.ToList();
                var bucketQuoted = colList.FirstOrDefault(i => i.PropertyInfo?.ColumnNameQuoted != null)?.PropertyInfo?.ColumnNameQuoted ?? db.AddQuote(bucket);

                string getBucketSeg(T ent) => GetSetJsonSeg(bucketQuoted, colList, col => accessor[ent, col.PropertyInfo.PropNamePure]);

                if (len > 1)
                {
                    //多行更新
                    if (colIndex > 0) sb.Append(',');
                    if (colList.All(i => i.FromSet))
                    {
                        //都是 .SetColumn(i=>i.Ext.Name,...) 行之间没有差异
                        var seg = getBucketSeg(null);
                        sb.Append("\r\n    ").Append(bucketQuoted).Append(" = ").Append(seg);
                    }
                    else
                    {
                        //行之间有差异 需要case...when
                        sb.Append("\r\n    ").Append(bucketQuoted).Append(" = ");
                        var caseSeg = GetCasePrimarySeg(primaryMulti, accessor, arr, getBucketSeg);
                        sb.Append(caseSeg);
                    }
                    colIndex++;
                }
                else
                {
                    //单行
                    var ent = arr.First();
                    var seg = getBucketSeg(ent);
                    if (colIndex > 0) sb.Append(',');
                    sb.Append("\r\n    ").Append(bucketQuoted).Append(" = ").Append(seg);
                    colIndex++;
                }
            }
        }
        //处理 setRaw
        if (SetRawItems.IsNotNullOrEmpty())
        {
            for (int i = 0; i < SetRawItems.Count; i++)
            {
                if (colIndex > 0) sb.Append(',');
                sb.Append("\r\n    ").Append(SetRawItems[i]);
                colIndex++;
            }
        }
        if (onlySet)
        {
            var _sql = sb.ToString();
            sb.Clear();
            return _sql;
        }

        //过滤条件
        var hasWhere = false;
        if (whereList.Count != 0)
        {
            if (len > 1)
            {
                //多行
                //联合主键: where (teacherId,studentId) in ((1,1),(1,2))
                //单主键: where id in (1,2)
                if (primaryMulti.Count > 1)
                {
                    sb.Append("\r\nwhere (").Append(primaryMulti.Select(i => i.ColumnNameQuoted).ToStringSeparated(",")).Append(") in (");
                    //vals: (1,1),(1,2)
                    var vals = arr.Select(ent =>
                    {
                        return "(" + primaryMulti.Select(i => db.ConvertToSqlSeg(accessor[ent, i.PropNamePure]).UnWrap()).ToStringSeparated(",") + ")";
                    }).ToStringSeparated(",");
                    sb.Append(vals).Append(')');
                }
                else
                {
                    var p = arr.Select(i => accessor[i, primary.PropNamePure]).ToArray();
                    var seg = db.ConvertToSqlSeg(p).UnWrap();
                    sb.Append("\r\nwhere ").Append(primary.ColumnNameQuoted).Append(" in ")
                        .Append(seg);
                }
                hasWhere = true;
            }
            else
            {
                //单行
                //联合主键: where teacherId=1 and studentId = 1
                //单主键: where id = 1
                if (primaryMulti.Count > 1)
                {
                    sb.Append("\r\nwhere ");
                    //vals: teacherId=1 and studentId = 1
                    var vals = primaryMulti.Select(i => i.ColumnNameQuoted + " = " + db.ConvertToSqlSeg(accessor[arr.FirstOrDefault(), i.PropNamePure]).UnWrap()).ToStringSeparated(" and ");
                    sb.Append(vals);
                }
                else
                {
                    sb.Append("\r\nwhere ").Append(primary.ColumnNameQuoted).Append(" = ")
                        .Append(db.ConvertToSqlSeg(accessor[arr.FirstOrDefault(), primary.PropNamePure]).UnWrap());
                }
                hasWhere = true;
            }
        }
        //应用Filters
        ApplyFilters(sb, hasWhere);
        sb.Append(';');
        var sql = sb.ToString();
        sb.Clear();
        return sql;
    }
    /// <summary>
    /// 给定json列名, 以及多个设置项(可能有从Entity上取值的,也可能从 SetColumn 取值得)
    /// </summary>
    private string GetSetJsonSeg(string bucket, List<SetListItem> colList, Func<SetListItem, object> getConstantVal, Dictionary<ParameterExpression, object> midValues = null, IList<ParameterExpression> parameters = null)
    {
        var sb2 = new StringBuilder();
        //先将所有的赋值操作集中到 totalParts
        List<PartValue> totalParts = null;
        //colList 的每一项可能是
        //1. 表示从 Entity 的对应列上取值
        //2. json列的直接值 如: SetColumn(i=>i.Ext,...) or ()=>new Person{ EXT =...}
        //3. json列的深度值 如: SetColumn(i=>i.Ext.Name,...)
        for (int j = 0; j < colList.Count; j++)
        {
            var col = colList[j];
            var val = col.FromSet ? col.Value : getConstantVal(col);
            //一个转换可能得到多个(从 new... 中分离的 json_set ),如:
            //SetColumn(i=>i.Ext,p=>new Ext{ Detail=new Detail{...} })
            //SetExpr(p=>new Person{ Ext = new Ext{ Detail=new Detail{...}})
            var _parts = ConvertToJsonFormatV2(val, midValues, parameters);
            if (col.JsonDepthProps.IsNotNullOrEmpty())
            {
                //将 SetColumn(i=>i.Ext.Detail.Info,...) 中的前置深度放到 part 中的 props 中
                for (int k = 0; k < _parts.Count; k++)
                {
                    var _part = _parts[k];
                    _part.Props ??= [];
                    _part.Props.InsertRange(0, col.JsonDepthProps);
                }
            }
            if (totalParts == null) totalParts = _parts;
            else totalParts.AddRange(_parts);
        }

        //处理 totalParts
        //防止重复处理,如下面两个只需要处理一次就行了:
        //SetColumn(i=>i.Ext.Detail.Age,...)
        //SetColumn(i=>i.Ext.Detail.Name,...)
        var hasDealNullObjs = new List<string>(32);
        //是否已在前面加了 json_set
        var hasJsonSet = false;
        //当前面有直接赋值的时候, 是否赋值了null 如: ()=>new Person{ Ext = null}
        var isBaseNull = false;
        for (int j = 0; j < totalParts.Count; j++)
        {
            var part = totalParts[j];
            //先求出当前part值
            var getSqlSeg = (EnumJsonAcceptAsType acceptType) =>
            {
                return part.Type switch
                {
                    EnumPartType.ConstantObject => db.ConvertJsonLiteralToSql(part.Value, acceptType).res,
                    EnumPartType.Seg => db.ConvertJsonVariableToSql(part.Value.ToString(), part.JsonDataType.Value, acceptType),
                    _ => throw new NotImplementedException()
                };
            };

            var props = part.Props;
            //没有深度属性, 直接全覆盖 如: set Ext='{...}'
            if (props.IsNullOrEmpty())
            {
                sb2.Clear();
                var seg = getSqlSeg(EnumJsonAcceptAsType.Doc);
                sb2.Append(seg);
                hasJsonSet = false;
                isBaseNull = seg == "null";
                continue;
            }

            //下面都是有深度属性的 如: set Ext=json_set('{...}',...)
            var sqlSeg = getSqlSeg(EnumJsonAcceptAsType.Value);

            //前面要加一次 json_set 函数
            if (!hasJsonSet)
            {
                //还没加 json_set
                hasJsonSet = true;
                if (sb2.Length == 0)
                {
                    //第一个就是 json_set
                    sb2.Append($"json_set(ifnull({bucket},json_object())");
                }
                else
                {
                    //现有全量赋值
                    if (isBaseNull)
                    {
                        sb2.Clear();
                        sb2.Append("json_set(json_object()");
                    }
                    else
                    {
                        sb2.Insert(0, $"json_set(");
                    }
                }
            }

            if (props?.Count > 1)
            {
                //防止前置 object 为null
                // SetColumn(i=>i.Ext.Detail.Info,...) => props:["Ext","Detail","Info"]
                //'$.Ext',ifnull(json_value(bucket,'$.Ext'),json_object())
                //'$.Ext.Detail',ifnull(json_value(bucket,'$.Ext.Detail'),json_object())
                for (int k = 0; k < props.Count - 1; k++)
                {
                    var _jsonKeyWrap = db.GetJsonPathSegByPropertyName(props.Take(k + 1));
                    if (hasDealNullObjs.IndexOf(_jsonKeyWrap) >= 0) continue;
                    hasDealNullObjs.Add(_jsonKeyWrap);
                    //注意: json_value 返回的是标量值
                    /* 看看下面的区别
                     -- 是期望的: {"Ext": {"Age": 20}}
                     select json_set(json_object(),
	                    '$.Ext',json_extract(json_object('name',json_object()),'$.name'),
	                    '$.Ext.Age',20
                    )
                    -- 意外: {"Ext": "{}"}
                    select json_set(json_object(),
	                    '$.Ext',json_value(json_object('name',json_object()),'$.name'),
	                    '$.Ext.Age',20
                    )
                     
                     */
                    sb2.Append($",\r\n{"        "}{_jsonKeyWrap},ifnull(json_extract({bucket},{_jsonKeyWrap}),json_object())");
                }
            }
            //当前赋值
            if (props.IsNullOrEmpty())
            {
                sb2.Append($"{sqlSeg}");
            }
            else
            {
                var jsonKeyWrap = db.GetJsonPathSegByPropertyName(props);
                sb2.Append($",\r\n{"        "}{jsonKeyWrap},{sqlSeg}");
            }
        }
        if (hasJsonSet) sb2.Append(')');
        var finalVal = sb2.ToString();
        sb2.Clear();
        return finalVal;
    }

    /// <summary>
    /// 返回格式(可以处理[JsonMap]类型的列, 因为json类型的列不好分组):
    /// <code>
    /// case id
    ///     when 1 then "小明"
    ///     when 2 then "小红"
    ///  end
    /// </code>
    /// 或
    /// <code>
    ///  case 
    ///     when teacherId = 1 and studentId = 1 then "小明"
    ///     when teacherId = 1 and studentId = 2 then "小红"
    ///  end
    /// </code>
    /// </summary>
    private string GetCasePrimarySeg(List<EntityPropertyInfo> primaryMulti, GenericAccessor<T> accessor, IEnumerable<T> arr, Func<T, string> getValSeg)
    {
        //联合主键: case when teacherId = 1 and studentId = 1 then 1 end,
        //单主键: case id when 1 then '1990-01-02 01:02:03' end,
        var sb = new StringBuilder();
        sb.Append("case ");
        var primary = primaryMulti.FirstOrDefault();
        if (primaryMulti.Count == 1) sb.Append(primary.ColumnNameQuoted);
        foreach (var ent in arr)
        {
            var seg = getValSeg(ent);
            if (primaryMulti.Count == 1)
            {
                var priVal = db.ConvertToSqlSeg(accessor[ent, primary.PropNamePure]).UnWrap();
                sb.Append("\r\n      when ").Append(priVal).Append(" then ").Append(seg);
            }
            else
            {
                sb.Append("\r\n      when ");
                for (int k = 0; k < primaryMulti.Count; k++)
                {
                    var pri = primaryMulti[k];
                    var priVal = db.ConvertToSqlSeg(accessor[ent, pri.PropNamePure]).UnWrap();
                    if (k > 0) sb.Append(" and ");
                    sb.Append(pri.ColumnNameQuoted).Append(" = ").Append(priVal);
                }
                sb.Append(" then ").Append(seg);
            }
        }
        sb.Append(" end");
        return sb.ToString();
    }

    /// <summary>
    /// 返回格式(主要是对普通列的值进行了分组, 防止出现过多的 case when):
    /// <code>
    /// case
    ///     when `Id` = 2 then 'name11'
    ///     when `Id` = 990 then 'name9891' end
    ///  end
    /// </code>
    /// 或
    /// <code>
    ///  case 
    ///     when (`teacherId`,`studentId`) in ((1,1),(1,2)) then 'Test'
    ///     when (`teacherId`,`studentId`) in ((2,1),(2,2)) then 'Test2' end
    /// </code>
    /// </summary>
    private string GetCasePrimarySegV2(List<EntityPropertyInfo> primaryMulti, List<List<(string propertyName, object value)>> vals, bool enum2String, string timeFormat)
    {
        //联合主键-单行: case when (`Id1`,`Id2`) = (1,1) then 1 end,
        //联合主键-多行: case when (`Id1`,`Id2`) in ((1,1),(1,2)) then 1 end,
        //单主键-单行: case when `Id` = 2 then 'name11' end,
        //单主键-多行: case when `Id` inm (1,2) then 'name11' end,
        var gs = vals.GroupBy(i => i.Last().value).ToList();
        if (gs.Count == 1) return db.ConvertToSqlSeg(gs[0].Key, enum2String ? true : timeFormat).UnWrap();
        var sb = new StringBuilder();
        sb.Append("case");
        for (int i = 0; i < gs.Count; i++)
        {
            var g = gs[i];
            var updateColumnValue = g.Key;
            var seg = db.ConvertToSqlSeg(updateColumnValue, enum2String ? true : timeFormat).UnWrap();
            var priValues = g.Select(i => i.SkipLast(1).Select(i => i.value)).ToList();

            if (primaryMulti.Count == 1)
            {
                //单列主键
                if (priValues.Count == 1)
                {
                    //单行
                    sb.Append($"\r\n      when {primaryMulti.First().ColumnNameQuoted} = {db.ConvertToSqlSeg(priValues[0].First()).UnWrap()}").Append(" then ").Append(seg);
                }
                else
                {
                    //多行
                    sb.Append($"\r\n      when {primaryMulti.First().ColumnNameQuoted} in ").Append(db.ConvertToSqlSeg(priValues.Select(i => i.First())).UnWrap()).Append(" then ").Append(seg);
                }
            }
            else
            {
                //多列主键
                if (priValues.Count == 1)
                {
                    //单行
                    sb.Append($"\r\n      when ({primaryMulti.Select(i => i.ColumnNameQuoted).ToStringSeparated(",")}) = {db.ConvertToSqlSeg(priValues[0]).UnWrap()}").Append(" then ").Append(seg);
                }
                else
                {
                    //多行
                    sb.Append($"\r\n      when ({primaryMulti.Select(i => i.ColumnNameQuoted).ToStringSeparated(",")}) in ").Append('(').Append(priValues.Select(i => db.ConvertToSqlSeg(i).UnWrap()).ToStringSeparated(",")).Append(')').Append(" then ").Append(seg);
                }
            }
        }
        sb.Append(" end");
        var sql = sb.ToString();
        sb.Clear();
        return sql;
    }
    #endregion

    #region ConvertToJsonFormat
    ///// <summary>
    ///// 将常量或表达式转为指定的json set 格式, 比如:
    ///// <list type="bullet">
    ///// <item>将常量 【tom】 转为 json doc 格式: 【'$','"tom"'】</item>
    ///// <item>将常量 【tom】 转为 json value 格式: 【'$.name','tom'】</item>
    ///// <item>将未简化的lambda 【()=>new { Id=1, Name="tom" }】 转为 json doc 格式: 【'$.Id',1,'$.Name','tom'】</item>
    ///// <item>将简化后的assign部分 【Name=Pram_0 }】 转为 json_set 格式: </item>
    ///// </list>
    ///// </summary>
    ///// <remarks>注意: 返回的 isDirect 表示是否直接赋值给column还是通过 json_set(ext,\r\n'$.age',50) 这种局部更新的方式</remarks>
    //internal (string res, EnumJsonDataType jsonDataType) ConvertToJsonFormat(object obj, Dictionary<ParameterExpression, object> midValues = null, string bucket = null, IEnumerable<string> jsonKeys = null, IList<ParameterExpression> parameters = null)
    //{
    //    var jsonPathWrap = jsonKeys.IsNotNullOrEmpty() ? db.GetJsonPathSegByPropertyName(jsonKeys) : null;
    //    if (obj is not Expression)
    //    {
    //        //常量
    //        return ConvertToJsonFormatByConstant(obj, jsonKeys);
    //    }
    //    var exp = obj as Expression;
    //    //assign的部分仍然可能是个常量
    //    var ret = BuilderHelper.GetConstant(exp, midValues);
    //    if (ret.Success)
    //    {
    //        //常量
    //        return ConvertToJsonFormatByConstant(ret.Data, jsonKeys);
    //    }
    //    if (exp is LambdaExpression lambda)
    //    {
    //        //lambda 进行简化
    //        var reduce = ExpressionHelper.ReduceLambda(lambda, BuilderHelper.IsKeepCallBackForUpdate);
    //        exp = reduce.exp;
    //        midValues = reduce.midValues;
    //        ret = BuilderHelper.GetConstant(exp, midValues);
    //        if (ret.Success)
    //        {
    //            //常量
    //            return ConvertToJsonFormatByConstant(ret.Data, jsonKeys);
    //        }
    //        //简化后不是常量
    //        //...
    //        lambda = exp as LambdaExpression;
    //        parameters = lambda.Parameters;
    //        exp = lambda.Body;
    //    }
    //    //解析表达式
    //    var jsonDataType = db.ParseJsonDataType(exp);
    //    if (exp.NodeType == ExpressionType.MemberInit || (exp.NodeType == ExpressionType.New && exp.Type.IsAnonymous()))
    //    {
    //        //最外层是局部更新 启用 json_set
    //        //db.Update<Person>(i=>new Person{Ext=new Ext{Name="tom"}}) 此时, Ext 可能是独占的, 但更新的时候也是局部更新, 需要用到 json_set()
    //        var stack = new List<string>();
    //        if (jsonKeys.IsNotNullOrEmpty()) stack.Add(jsonKeys);
    //        var sb = new StringBuilder();

    //        Action<Expression> func = null;
    //        func = (Expression exp) =>
    //        {
    //            var index = 0;
    //            var list = new List<(string propName, Expression exp)>();
    //            if (exp is NewExpression newExpression)
    //            {
    //                for (int i = 0; i < newExpression.Members.Count; i++)
    //                {
    //                    list.Add((newExpression.Members[i].Name, newExpression.Arguments[i]));
    //                }
    //            }
    //            else
    //            {
    //                var init = exp as MemberInitExpression;
    //                foreach (var bind in init.Bindings)
    //                {
    //                    var assignment = bind as MemberAssignment;
    //                    list.Add((assignment.Member.Name, assignment.Expression));
    //                }
    //            }
    //            foreach (var (propName, exp2) in list)
    //            {
    //                if (index > 0) sb.Append(',');
    //                var dataType = db.ParseJsonDataType(exp2);
    //                stack.Add(propName);
    //                var jsonPathWrap = db.GetJsonPathSegByPropertyName(stack);
    //                if (dataType == EnumJsonDataType.Object || dataType == EnumJsonDataType.Array)
    //                {
    //                    //防止为null
    //                    var _t = dataType == EnumJsonDataType.Object ? "json_object()" : "json_array()";
    //                    sb.Append($"\r\n{" ".Repeat(8)}{jsonPathWrap},cast(ifnull(json_value({bucket},{jsonPathWrap}),{_t}) as json),");
    //                }
    //                if (exp2.NodeType == ExpressionType.MemberInit || (exp2.NodeType == ExpressionType.New && exp2.Type.IsAnonymous()))
    //                {
    //                    func(exp2);
    //                }
    //                else
    //                {
    //                    //不再是局部更新
    //                    //最外层都不是局部更新 不用 json_set
    //                    var jsonDataType = db.ParseJsonDataType(exp2);
    //                    var seg = BuilderHelper.ParseSql(
    //                        expression: exp2,
    //                        db: db,
    //                        aliasesMap: parameters?.Select(i => new KeyValuePair<ParameterExpression, string>(parameters.FirstOrDefault(), null)),
    //                        parameters: parameters,
    //                        nodeCallBacks: [new NodeCallBack(BuilderHelper.WriteCallBackCondition, BuilderHelper.WriteCallBack(db, midValues))],
    //                        midValues: midValues);
    //                    var jsonSeg = db.ConvertJsonVariableToSql(seg, jsonDataType, EnumJsonAcceptAsType.Value);
    //                    sb.Append($"\r\n{" ".Repeat(8)}{jsonPathWrap},{jsonSeg}");
    //                }
    //                stack.RemoveAt(stack.Count - 1);
    //                index++;
    //            }
    //        };
    //        func(exp);
    //        return (sb.ToString(), jsonDataType);
    //    }
    //    else
    //    {
    //        //最外层都不是局部更新 不用 json_set
    //        var seg = BuilderHelper.ParseSql(
    //            expression: exp,
    //            db: db,
    //            aliasesMap: [new KeyValuePair<ParameterExpression, string>(parameters.FirstOrDefault(), null)],
    //            parameters: parameters,
    //            nodeCallBacks: [new NodeCallBack(BuilderHelper.WriteCallBackCondition, BuilderHelper.WriteCallBack(db, midValues))],
    //            midValues: midValues);
    //        if (jsonKeys.IsNotNullOrEmpty())
    //        {
    //            var jsonSeg = db.ConvertJsonVariableToSql(seg, jsonDataType, EnumJsonAcceptAsType.Value);
    //            return ($"\r\n{" ".Repeat(8)}{jsonPathWrap},{jsonSeg}", jsonDataType);
    //        }
    //        else
    //        {
    //            var jsonSeg = db.ConvertJsonVariableToSql(seg, jsonDataType, EnumJsonAcceptAsType.Doc);
    //            return (jsonSeg, jsonDataType);
    //        }
    //    }
    //}

    internal (string res, EnumJsonDataType jsonDataType) ConvertToJsonFormatByConstant(object constantVal, IEnumerable<string> jsonKeys = null)
    {
        //常量
        if (jsonKeys.IsNotNullOrEmpty())
        {
            //非独占 需要返回 json_set 的部分, 如: '$.name','jack'
            var jsonPathWrap = db.GetJsonPathSegByPropertyName(jsonKeys);
            var (val, jsonType) = db.ConvertJsonLiteralToSql(constantVal, EnumJsonAcceptAsType.Value);
            return ($"\r\n{" ".Repeat(8)}{jsonPathWrap},{val}", jsonType);
        }
        else
        {
            //独占 直接相等赋值就行,如: properties='"tom"', 不用 json_set
            var (val, jsonType) = db.ConvertJsonLiteralToSql(constantVal, EnumJsonAcceptAsType.Doc);
            return (val, jsonType);
        }
    }
    #endregion

    #region ConvertToJsonFormatV2
    internal List<PartValue> ConvertToJsonFormatV2(object obj, Dictionary<ParameterExpression, object> midValues = null, IList<ParameterExpression> parameters = null)
    {
        if (obj is not Expression)
        {
            //常量
            return [new PartValue { Type = EnumPartType.ConstantObject, Value = obj }];
        }
        var exp = obj as Expression;
        //assign的部分仍然可能是个常量
        var ret = BuilderHelper.GetConstant(exp, midValues);
        if (ret.Success)
        {
            //常量
            return [new PartValue { Type = EnumPartType.ConstantObject, Value = ret.Data }];
        }
        if (exp is LambdaExpression lambda)
        {
            //lambda 进行简化
            var reduce = ExpressionHelper.ReduceLambda(lambda, BuilderHelper.IsKeepCallBackForUpdate);
            exp = reduce.exp;
            midValues = reduce.midValues;
            ret = BuilderHelper.GetConstant(exp, midValues);
            if (ret.Success)
            {
                //lambda简化后仍然可能是个常量
                return [new PartValue { Type = EnumPartType.ConstantObject, Value = ret.Data }];
            }
            //简化后不是常量
            //...
            lambda = exp as LambdaExpression;
            parameters = lambda.Parameters;
            exp = lambda.Body;
        }
        //解析表达式
        var jsonDataType = db.ParseJsonDataType(exp);
        if (exp.NodeType == ExpressionType.MemberInit || (exp.NodeType == ExpressionType.New && exp.Type.IsAnonymous()))
        {
            //最外层是局部更新 启用 json_set
            //db.Update<Person>(i=>new Person{Ext=new Ext{Name="tom"}})
            //db.Update<Person>().SetColumn(i=>i.Ext.Detail,i=>new Person{Ext=new Ext{Name="tom"}})
            var stack = new List<string>();
            var partList = new List<PartValue>();

            Action<Expression> func = null;
            func = (Expression exp) =>
            {
                var list = new List<(string propName, Expression exp)>();
                if (exp is NewExpression newExpression)
                {
                    for (int i = 0; i < newExpression.Members.Count; i++)
                    {
                        list.Add((newExpression.Members[i].Name, newExpression.Arguments[i]));
                    }
                }
                else
                {
                    var init = exp as MemberInitExpression;
                    foreach (var bind in init.Bindings)
                    {
                        var assignment = bind as MemberAssignment;
                        list.Add((assignment.Member.Name, assignment.Expression));
                    }
                }
                foreach (var (propName, exp2) in list)
                {
                    stack.Add(propName);
                    if (exp2.NodeType == ExpressionType.MemberInit || (exp2.NodeType == ExpressionType.New && exp2.Type.IsAnonymous()))
                    {
                        func(exp2);
                    }
                    else
                    {
                        //不再是局部更新
                        //最外层都不是局部更新 不用 json_set
                        var jsonDataType = db.ParseJsonDataType(exp2);
                        var seg = BuilderHelper.ParseSql(
                            expression: exp2,
                            db: db,
                            aliasesMap: parameters?.Select(i => new KeyValuePair<ParameterExpression, string>(parameters.FirstOrDefault(), null)),
                            parameters: parameters,
                            nodeCallBacks: [new NodeCallBack(BuilderHelper.WriteCallBackCondition, BuilderHelper.WriteCallBack(db, midValues))],
                            midValues: midValues);
                        partList.Add(new PartValue { Type = EnumPartType.Seg, Value = seg, JsonDataType = jsonDataType, Props = [.. stack] });
                    }
                    stack.RemoveAt(stack.Count - 1);
                }
            };
            func(exp);
            return partList;
        }
        else
        {
            //最外层都不是局部更新 不用 json_set
            var seg = BuilderHelper.ParseSql(
                expression: exp,
                db: db,
                aliasesMap: parameters.IsNotNullOrEmpty() ? [new KeyValuePair<ParameterExpression, string>(parameters.FirstOrDefault(), null)] : null,
                parameters: parameters,
                nodeCallBacks: [new NodeCallBack(BuilderHelper.WriteCallBackCondition, BuilderHelper.WriteCallBack(db, midValues))],
                midValues: midValues);
            return [new PartValue { JsonDataType = jsonDataType, Type = EnumPartType.Seg, Value = seg }];
        }
    }
    internal class PartValue
    {
        public List<string> Props { get; set; }
        public object Value { get; set; }
        public EnumJsonDataType? JsonDataType { get; set; }
        public EnumPartType Type { get; set; }
    }
    internal enum EnumPartType
    {
        ConstantObject, Seg
    }
    #endregion

    #region 执行
    /// <summary>
    /// 插入并返回受影响的行数
    /// </summary>
    public override int ExecuteAffrows()
    {
        var sql = ToSql();
        return RunWriteMonitor(new AfterWriteArgument
        {
            EntityInfo = EntityInfo,
            TableName = TableName.FullNameQuoted,
            WriteType = EnumWriteType.Update
        }, () => db.ExecuteSql(sql, CommandType.Text, TimeoutSeconds));
    }

    /// <summary>
    /// 插入并返回受影响的行数
    /// </summary>
    public override async Task<int> ExecuteAffrowsAsync(CancellationToken cancellationToken = default)
    {
        var sql = ToSql();
        return await RunWriteMonitorAsync(new AfterWriteArgument
        {
            EntityInfo = EntityInfo,
            TableName = TableName.FullNameQuoted,
            WriteType = EnumWriteType.Update
        }, async () => await db.ExecuteSqlAsync(sql, CommandType.Text, TimeoutSeconds, null, cancellationToken));
    }
    #endregion
}